package com.data.reports.export.excel;

import com.data.reports.export.excel.annotation.ExcelField;
import com.data.reports.export.excel.annotation.ExcelSheet;
import com.data.reports.export.excel.handler.AbstractExporter;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;

import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.ThreadFactory;

/**
 * Excel导出工具
 *
 * @author hujifang 2017-09-08 22:27:20
 */
public class ExcelExportUtil {

    private static ExecutorService executorService= Executors.newFixedThreadPool(5,new NameThreadFactory("ExcelExporter"));

    private static class NameThreadFactory implements ThreadFactory{

        private String prefix;

        private ThreadGroup threadGroup;

        public NameThreadFactory(String prefix){
            this.prefix=prefix;
            SecurityManager securityManager=System.getSecurityManager();
            threadGroup=Thread.currentThread().getThreadGroup()!=null?Thread.currentThread().getThreadGroup():securityManager.getThreadGroup();
        }

        @Override
        public Thread newThread(Runnable r) {
            //String threadName=
            return new Thread();
        }
    }


    /**
     * 导出Excel对象
     *
     * @param dataList  Excel数据
     * @return
     */
    public static Workbook exportWorkbook(List<?> dataList){

        // data
        if (dataList==null || dataList.size()==0) {
            throw new IllegalArgumentException("data can not be empty.");
        }

        // sheet
        Class<?> sheetClass = dataList.get(0).getClass();
        ExcelSheet excelSheet = sheetClass.getAnnotation(ExcelSheet.class);

        String sheetName = dataList.get(0).getClass().getSimpleName();
        HSSFColor.HSSFColorPredefined headColor = null;
        if (excelSheet != null) {
            if (excelSheet.name()!=null && excelSheet.name().trim().length()>0) {
                sheetName = excelSheet.name().trim();
            }
            headColor = excelSheet.headColor();
        }


        // sheet field
        List<Field> fields = new ArrayList<>();
        if (sheetClass.getDeclaredFields()!=null && sheetClass.getDeclaredFields().length>0) {
            for (Field field: sheetClass.getDeclaredFields()) {
                if (Modifier.isStatic(field.getModifiers())) {
                    continue;
                }
                fields.add(field);
            }
        }

        if (fields.size() == 0) {
            throw new IllegalArgumentException("data field can not be empty.");
        }

        // book
        Workbook workbook = new HSSFWorkbook();     // HSSFWorkbook=2003/xls、XSSFWorkbook=2007/xlsx
        Sheet sheet = workbook.createSheet(sheetName);

        // sheet header row
        CellStyle headStyle = null;
        if (headColor != null) {
            headStyle = workbook.createCellStyle();
            /*Font headFont = book.createFont();
            headFont.setColor(headColor);
            headStyle.setFont(headFont);*/

            headStyle.setFillForegroundColor(headColor.getIndex());
            headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headStyle.setFillBackgroundColor(headColor.getIndex());
        }

        Row headRow = sheet.createRow(0);
        for (int i = 0; i < fields.size(); i++) {
            Field field = fields.get(i);
            ExcelField excelField = field.getAnnotation(ExcelField.class);
            String fieldName = (excelField!=null && excelField.name()!=null && excelField.name().trim().length()>0)?excelField.name():field.getName();

            Cell cellX = headRow.createCell(i, CellType.STRING);
            if (headStyle != null) {
                cellX.setCellStyle(headStyle);
            }
            cellX.setCellValue(String.valueOf(fieldName));
        }

        // sheet data rows
        for (int dataIndex = 0; dataIndex < dataList.size(); dataIndex++) {
            int rowIndex = dataIndex+1;
            Object rowData = dataList.get(dataIndex);

            Row rowX = sheet.createRow(rowIndex);

            for (int i = 0; i < fields.size(); i++) {
                Field field = fields.get(i);
                try {
                    field.setAccessible(true);
                    Object fieldValue = field.get(rowData);

                    Cell cellX = rowX.createCell(i, CellType.STRING);
                    cellX.setCellValue(String.valueOf(fieldValue));
                } catch (IllegalAccessException e) {
                    throw new IllegalArgumentException(e);
                }
            }
        }

        return workbook;
    }


    /**
     * 导出工作簿 Excel
     * @param workbook 工作簿
     * @param abstractExporter 导出数据
     * @return Workbook
     */
    public static Workbook exportWorkbook(Workbook workbook,AbstractExporter abstractExporter) {
        // data
        if (abstractExporter==null || abstractExporter.size()==0) {
            throw new IllegalArgumentException("data can not be empty.");
        }
        //header
        if (abstractExporter.getHeader()==null || abstractExporter.getHeader().length<=0) {
            throw new IllegalArgumentException("header can not be empty.");
        }

        if (workbook==null){
            workbook = new SXSSFWorkbook(1000);
        }


        // sheet
        Class<?> sheetClass = abstractExporter.getClass();

        String sheetName=abstractExporter.getSheetName();
        HSSFColor.HSSFColorPredefined headColor = abstractExporter.getHeadColor();
        // sheet header row
        CellStyle headStyle = null;
        if (headColor != null) {
            headStyle = workbook.createCellStyle();

            headStyle.setFillForegroundColor(headColor.getIndex());
            headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headStyle.setFillBackgroundColor(headColor.getIndex());
        }

        sheetName = StringUtils.isBlank(sheetName) ? sheetClass.getSimpleName() : sheetName;

        //处理分页
        Map<Integer /*sheet start*/,Integer /*sheet end*/> sheetPageSizeMap=new LinkedHashMap<>();
        for (int i=0;i<abstractExporter.size();i+=abstractExporter.getSheetPageSize()){
            sheetPageSizeMap.put(i,Math.min(i+abstractExporter.getSheetPageSize(),abstractExporter.size()));
        }
        if (sheetPageSizeMap.size()==0){
            Sheet sheet = workbook.createSheet(sheetName);
            writeRow(sheet,0,abstractExporter.getHeader(),headStyle);
            return workbook;
        }

        CountDownLatch countDownLatch=new CountDownLatch(sheetPageSizeMap.size());
        for (Map.Entry<Integer, Integer> entry : sheetPageSizeMap.entrySet()) {

            countDownLatch.countDown();
        }
        Sheet sheet = workbook.createSheet(sheetName);


        writeRow(sheet,0,abstractExporter.getHeader(),headStyle);

        // sheet data rows
        for (int dataIndex = 0; dataIndex < abstractExporter.size(); dataIndex++) {
            int rowIndex = dataIndex+1;
            String[] rowData = abstractExporter.makeRow(dataIndex);

            writeRow(sheet,rowIndex,rowData);
        }

        return workbook;
    }

    private static void writeRow(Sheet sheet, int rowNumber, String[] data,CellStyle cellStyle) {
        Row row = sheet.createRow(rowNumber);
        for (int i = 0; i < data.length; i++) {
            Cell nameCell = row.createCell(i,CellType.STRING);
            if (cellStyle != null) {
                nameCell.setCellStyle(cellStyle);
            }
            nameCell.setCellValue(data[i]);
        }
    }

    private static void writeRow(Sheet sheet, int rowNumber, String[] data) {
        Row row = sheet.createRow(rowNumber);
        for (int i = 0; i < data.length; i++) {
            Cell nameCell = row.createCell(i,CellType.STRING);
            nameCell.setCellValue(data[i]);
        }
    }

    /**
     * 导出工作簿 Excel
     * @param sqlRowSet 数据游标集合
     * @param sheetName 工作簿名称
     * @param sheetPageSize 工作簿分页大小
     * @return Workbook
     */
    public static Workbook resultSetToExcel(SqlRowSet sqlRowSet, String sheetName, int sheetPageSize) {
        //工作簿每写入1000行数据，就把数据写入磁盘
        Workbook workbook = new SXSSFWorkbook(1000);
        Sheet sheet;
        int rowNo = 0;      //总行号
        int pageRowNo = 0;  //页行号

        Row row;
        // sheet header row
        HSSFColor.HSSFColorPredefined headColor=HSSFColor.HSSFColorPredefined.LIGHT_GREEN;
        CellStyle headStyle = workbook.createCellStyle();
        headStyle.setFillForegroundColor(headColor.getIndex());
        headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headStyle.setFillBackgroundColor(headColor.getIndex());

        Cell cell;
        SqlRowSetMetaData md=sqlRowSet.getMetaData();
        int nColumn=md.getColumnCount();

        sheetName= StringUtils.isBlank(sheetName)?"Sheet":sheetName.replaceAll("[\\[\\]\\*\\?\\\\:/]*","");
        while (sqlRowSet.next()){
            if (rowNo % sheetPageSize == 0){
                workbook.createSheet(sheetName+rowNo/sheetPageSize);
                pageRowNo = 0;      //每当新建了工作表就将当前工作表的行号重置为0
            }
            sheet=workbook.getSheetAt(rowNo/sheetPageSize);
            //写入header
            if (pageRowNo == 0) {
                row = sheet.createRow(pageRowNo);
                // 写入各个字段的名称
                for (int i = 1; i <= nColumn; i++) {
                    cell = row.createCell(i - 1);
                    cell.setCellStyle(headStyle);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(md.getColumnLabel(i));
                }
                pageRowNo++;
            }
            row = sheet.createRow(pageRowNo);
            for (int j = 1; j <= nColumn; j++) {
                cell = row.createCell(j - 1);
                cell.setCellType(CellType.STRING);
                Object oj = sqlRowSet.getObject(j);
                if (oj == null) {
                    oj = "";
                }
                cell.setCellValue(oj.toString());
            }
            pageRowNo++;
            rowNo++;
        }
        return workbook;
    }

    /**
     * 导出工作簿二进制格式
     * @param sqlRowSet 数据游标集合
     * @param sheetName 工作簿名称
     * @param sheetPageSize 工作簿分页大小
     */
    public static byte[] resultSetToExcelBytes(SqlRowSet sqlRowSet, String sheetName, int sheetPageSize) {
        // workbook
        Workbook workbook = resultSetToExcel(sqlRowSet, sheetName,sheetPageSize);

        ByteArrayOutputStream byteArrayOutputStream = null;
        byte[] result;
        try {
            // workbook 2 ByteArrayOutputStream
            byteArrayOutputStream = new ByteArrayOutputStream();
            workbook.write(byteArrayOutputStream);

            // flush
            byteArrayOutputStream.flush();

            result = byteArrayOutputStream.toByteArray();
            return result;
        } catch (Exception e) {
            throw new IllegalArgumentException(e);
        } finally {
            try {
                if (byteArrayOutputStream != null) {
                    byteArrayOutputStream.close();
                }
            } catch (Exception e) {
                throw new IllegalArgumentException(e);
            }
        }
    }


    /**
     * 导出Excel文件到磁盘
     *
     * @param dataList
     * @param filePath
     */
    public static void exportToFile(List<?> dataList, String filePath){
        // workbook
        Workbook workbook = exportWorkbook(dataList);

        FileOutputStream fileOutputStream = null;
        try {
            // workbook 2 FileOutputStream
            fileOutputStream = new FileOutputStream(filePath);
            workbook.write(fileOutputStream);

            // flush
            fileOutputStream.flush();
        } catch (Exception e) {
            throw new IllegalArgumentException(e);
        } finally {
            try {
                if (fileOutputStream!=null) {
                    fileOutputStream.close();
                }
            } catch (Exception e) {
                throw new IllegalArgumentException(e);
            }
        }
    }

    /**
     * 导出Excel字节数据
     *
     * @param dataList
     * @return
     */
    public static byte[] exportToBytes(List<?> dataList){
        // workbook
        Workbook workbook = exportWorkbook(dataList);

        ByteArrayOutputStream byteArrayOutputStream = null;
        byte[] result;
        try {
            // workbook 2 ByteArrayOutputStream
            byteArrayOutputStream = new ByteArrayOutputStream();
            workbook.write(byteArrayOutputStream);

            // flush
            byteArrayOutputStream.flush();

            result = byteArrayOutputStream.toByteArray();
            return result;
        } catch (Exception e) {
            throw new IllegalArgumentException(e);
        } finally {
            try {
                if (byteArrayOutputStream != null) {
                    byteArrayOutputStream.close();
                }
            } catch (Exception e) {
                throw new IllegalArgumentException(e);
            }
        }
    }

}
